package cz.acies.uniql.metadata;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.*;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.dom.DOMElement;


import cz.acies.uniql.dialect.Dialect;
import cz.acies.uniql.dialect.PostgreDialect;
import cz.acies.uniql.sql.AlterTable;
import cz.acies.uniql.sql.CreateTable;

/**
 * class TableMetaData
 * 
 * T��da sdru�uje v�echna metadata dan� tabulky (FieldMetaData, IndexMetaData a 
 * PrimaryKeyMetaData)
 * 
 */
public class TableMetaData implements Serializable
{
	private static final long serialVersionUID = 2120590674654216368L;
	
	private String name = null;
	private String schema = null;
	private String catalog = null;

	private PrimaryKeyMetaData primarykey = null;
	private IndexesMetaData indexes = null;
	private Vector<FieldMetaData> fields = null;
	private HashMap<String, Integer> columnIndex = null;
	private LinkedHashMap<String, FieldMetaData> columns = new LinkedHashMap<String, FieldMetaData>(); 

	//private Map foreignKeys = new HashMap();
	//private Map uniqueKeys = new HashMap();

	private Dialect dialect = null;
	private DBMetaData dbmd = null;

	// ----- -----------------------------------------------------------------

	/**
	 * 
	 */
	public TableMetaData() {
	}
	
	public TableMetaData(Dialect dialect, DBMetaData dbmd) {
		this.dialect = dialect;
		this.dbmd = dbmd;
	}

	public void parse(String table, Connection conn) throws Exception {
		ResultSet rs = null;
		try {
			setName(table);
			DatabaseMetaData dbmd = conn.getMetaData();
			String schema = this.getDialect().getSchema();
			
			if (table.startsWith("BIN$")) return;
			try {
				Dialect dialect = this.getDialect();
				table = dialect.tableNameToCase(table);
				
				String catalog = null;
				if (dialect instanceof PostgreDialect) {
					catalog = conn.getCatalog();
				}
				rs = dbmd.getColumns(catalog, schema, table, null);
				//System.out.println("TableMetaData.parse().rs = "+rs);
				//System.out.println("" + ConnectorHelper.printColumnsMessage(rs));
				while (rs.next()) {
					FieldMetaData fmd = new FieldMetaData();
					fmd.setDialect(getDialect());
					fmd.setDBMetaData(dbmd());
					fmd.setTableMetaData(this);
					fmd.parse(table, rs);
					addField(fmd);
				}
				
			} catch (Exception e) {
				System.out.println("TableMetaData.parse().FIELDS.ERROR: ("+table+") "+e.getMessage());
			} finally {
				try { if (rs != null) rs.close(); } catch (Exception e) {}
			}

			try {			
				rs = dbmd.getPrimaryKeys(null, schema, table);
				while (rs.next()) {	
					//System.out.println("TableMetaData.parse().PK: table = "+rs.getString("TABLE_NAME")+", column = "+rs.getString("COLUMN_NAME"));
					PrimaryKeyMetaData pkmd = new PrimaryKeyMetaData();
					pkmd.setDialect(getDialect());
					pkmd.setColumn(rs.getString("COLUMN_NAME"));
					pkmd.setTable(rs.getString("TABLE_NAME"));
					pkmd.setPkName("PK_"+table);
					this.setPrimaryKey(pkmd);
				}
			} catch (Exception e) {
				System.out.println("TableMetaData.parse().PRIMARY_KEY.ERROR: ("+table+") "+e.getMessage());
			} finally {
				try { if (rs != null) rs.close(); } catch (Exception e) {}
			}
			
			try {
				rs = dbmd.getIndexInfo(null, schema, table, false, true);
				while (rs.next()) {
					IndexesMetaData idxmd = new IndexesMetaData();
					this.setIndexes(idxmd);
					idxmd.setDialect(this.getDialect());
					idxmd.parse(table, rs);
				}
			} catch (Exception e) {
				System.out.println("TableMetaData.parse().INDEX.ERROR: ("+table+") "+e.getMessage());
			} finally {
				try { if (rs != null) rs.close(); } catch (Exception e) {}
			}

		} catch (Exception exc) {
			System.out.println("TableMetaData.parse() ERROR: " + exc.getMessage());
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception e) {}			
		}
	}

	// -----  -----------------------------------------------------------------
	
	public int updateStructureColumn(FieldMetaData fmd, boolean isIndex, String actionType, Connection conn) throws Exception {
		actionType = this.qualifyAlterTableActionType(actionType);
		if (actionType.equals(AlterTable.ADD_COLUMN)) {
			try {
				if (fmd != null) {
					String columnName = fmd.getName();
					if (columnIndex.containsKey(columnName)) {				
						return -1; 
					} else {
						String table = this.name;
						AlterTable alterTable = new AlterTable(this.dialect, dbmd())
						.setTableName(table)
						.setActionType(AlterTable.ADD_COLUMN)
						.addField(fmd);
						getDialect().execAlterTable(alterTable, conn);
						addField(fmd);
						if (isIndex) {
							updateStructureColumnIndex(conn, columnName, true);
						}
						return 1;
					}
				} else {
					throw new Exception("TableMetaData.updateStructureColumn(): FieldMetaData is null...");
				}
			} catch (Exception exc) {
				exc.printStackTrace();
			}
			return -1; 
		} else if (actionType.equals(AlterTable.CHANGE_COLUMN)) {
			try {
				if (fmd != null) {
					String columnName = fmd.getName();
					if (columnIndex.containsKey(columnName)) {
						//FieldMetaData origFmd = this.getFieldByName(columnName);
						String table = this.name;
						AlterTable alterTable = new AlterTable(this.dialect, dbmd())
						.setTableName(table)
						.setActionType(AlterTable.CHANGE_COLUMN)
						.addField(fmd);
						getDialect().execAlterTable(alterTable, conn);
						addField(fmd);
						//updateStructureColumnIndex(conn, columnName, isIndex);
						return 1;
					} else {						
						System.out.println("TableMetaData.updateStructureColumn( CHANGE ):\n" + "Column( "+columnName+" ) in table( "+fmd.getTable()+" ) is not exists...");				
						return -1; 
					}
				} else {
					throw new Exception("TableMetaData.updateStructureColumn( CHANGE ): FieldMetaData is null...");
				}
			} catch (Exception exc) {
				//exc.printStackTrace();
				throw exc;
			}
//$			return -1;
		} else if (actionType.equals(AlterTable.DROP_COLUMN)) {
			return -1;
		}
		return -1;
	}

	public int updateStructureColumn( 
		String columnName, 
		int sqlType, 
		int size, 
		int decimal, 
		boolean nullable, 
		String value,
		boolean pk, 
		boolean unique, 
		boolean autoincrement, 
		boolean isIndex, 
		String actionType, 
		Connection conn) 
	throws Exception {
		actionType = this.qualifyAlterTableActionType(actionType);
		FieldMetaData fmd = getFieldByName(columnName);
		if (actionType.equals(AlterTable.ADD_COLUMN)) {			
			if (fmd == null) {
				fmd = new FieldMetaData(getDialect(), dbmd());
				fmd.set(getName(), columnName, sqlType, 2, 0, false, value, pk, unique, autoincrement);
				return updateStructureColumn(fmd, isIndex, actionType, conn);		
			} else {
				System.out.println("TableMetaData.updateStructureColumn( ADD_COLUMN ):\n\t" + "Column ("+columnName+") in table ("+this.getName()+") is exists..."); 
			}
		} else if (actionType.equals("CHANGE_COLUMN")) {
			
		} else if (actionType.equals("DROP_COLUMN")) {
			fmd.set(getName(), columnName, sqlType, 2, 0, false, value, pk, unique, autoincrement);
			//System.out.println("TableMetaData.updateStructureColumn().fmd = "+fmd);
			return updateStructureColumn(fmd, isIndex, actionType, conn);			
		}
		return -1;
	}
	
	public int updateStructureColumn(String columnName, int sqlType, int size, int decimal, String value, boolean nullable, boolean isIndex, String typeOperation, Connection conn) throws Exception {
		return updateStructureColumn(columnName, sqlType, size, decimal, nullable, value, false, false, false, isIndex, typeOperation, conn);
	}
	
	private String qualifyAlterTableActionType(String type) {
		if (AlterTable.actionSet.contains(type.trim().toUpperCase())) { 
			return type; 
		}
		else if (type.equals("ADD") || type.trim().length() == 0) { return AlterTable.ADD_COLUMN; }
		else if (type.equals("CHANGE")) { return AlterTable.CHANGE_COLUMN; }
		else if (type.equals("DROP")) { return AlterTable.DROP_COLUMN; }
		else return ""+type;
	}

	// -----  -----------------------------------------------------------------
	
	public void updateStructureColumnIndex(Connection conn, String columnName, boolean isIndex) {		
		updateStructureColumnIndex(conn, columnName, isIndex, "");
	}

	public void updateStructureColumnIndex(Connection conn, String columnName, boolean isIndex, String type) {
		try {
			Map<String, Object> map = null;
			if (getIndexes() != null && !getIndexes().isEmpty()) {
				map = getIndexes().getIndexByColumnName(columnName);
			}
			AlterTable alterTable = new AlterTable(getDialect(), dbmd())
			.setTableName(getName());
			if (type.equalsIgnoreCase("FULLTEXT")) {
				if (isIndex && (map == null || map.isEmpty())) {
					Map<String, Object> index = new HashMap<String, Object>();
					Map<Integer, String> column = new HashMap<Integer, String>();
					column.put(1, columnName);
					index.put("columns", column);
					alterTable.setActionType(AlterTable.ADD_FULLTEXT);
					alterTable.addIndex(index);
					getDialect().execAlterTable(alterTable, conn);
				} else if (isIndex && (map != null)) {
					if (map.containsKey("columns")) {					
						Map columnsMap = (Map)map.get("columns");
						if (columnsMap != null && columnsMap.size() > 1) {
							Map<String, Object> index = new HashMap<String, Object>();
							Map<Integer, String> column = new HashMap<Integer, String>();
							column.put(1, columnName);
							index.put("columns", column);
							alterTable.setActionType(AlterTable.ADD_FULLTEXT);
							alterTable.addIndex(index);
							getDialect().execAlterTable(alterTable, conn);						
						}
					}
				} else {
					if (map != null && !map.isEmpty()) {
						alterTable.setActionType(AlterTable.DROP_FULLTEXT);
						alterTable.addIndex(map);
						getDialect().execAlterTable(alterTable, conn);
						getIndexes().remove(columnName);
					} else {
						Map<String, Object> index = new HashMap<String, Object>();
						Map<Integer, String> column = new HashMap<Integer, String>();
						column.put(1, columnName);
						index.put("columns", column);
						alterTable.setActionType(AlterTable.DROP_FULLTEXT);
						alterTable.addIndex(index);
						getDialect().execAlterTable(alterTable, conn);
						getIndexes().remove(columnName);						
					}
				}				
				//System.out.println("Indexes:" + getIndexes());
			} else {				
				if (isIndex && (map == null || map.isEmpty())) {
					Map<String, Object> index = new HashMap<String, Object>();
					Map<Integer, String> column = new HashMap<Integer, String>();
					column.put(1, columnName);
					index.put("columns", column);
					alterTable.setActionType(AlterTable.ADD_INDEX);
					alterTable.addIndex(index);
					getDialect().execAlterTable(alterTable, conn);
				} else if (isIndex && (map != null)) {
					if (map.containsKey("columns")) {					
						Map columnsMap = (Map)map.get("columns");
						if (columnsMap != null && columnsMap.size() > 1) {
							Map<String, Object> index = new HashMap<String, Object>();
							Map<Integer, String> column = new HashMap<Integer, String>();
							column.put(1, columnName);
							index.put("columns", column);
							alterTable.setActionType(AlterTable.ADD_INDEX);
							alterTable.addIndex(index);
							getDialect().execAlterTable(alterTable, conn);						
						}
					}
				} else {
					if (map != null && !map.isEmpty()) {
						alterTable.setActionType(AlterTable.DROP_INDEX);
						alterTable.addIndex(map);
						getDialect().execAlterTable(alterTable, conn);
					}
				}
			}
		} catch (Exception exc) {
			exc.printStackTrace();
			
		}
	}
	
	/**
	 * @param name
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return
	 */
	public String getName() {
		return this.name;
	}

	/**
	 * returns quoted name as it would be in the mapping file.
	 */
	public String getQuotedName() {
		return dialect.LQ() + name + dialect.RQ();
	}

	public String getQuotedName(Dialect d) {
		return dialect.LQ() + name + dialect.RQ();
	}

	// -----  -----------------------------------------------------------------
	
	/**
	 * returns quoted name as it is in the mapping file.
	 */
	public String getQuotedSchema() {
		return dialect.LQ() + schema + dialect.LQ(); 
	}

	public String getQuotedSchema(Dialect dialect) {
		return dialect.LQ() + schema + dialect.RQ();
	}

	public String getQualifiedName(Dialect dialect, DBMetaData dbmd) {
		String quotedName = getQuotedName( dialect );
		String usedSchema = (schema == null) ? dbmd.getSchema() : getQuotedSchema( dialect );
		String usedCatalog = (catalog == null) ? dbmd.getCatalog() : catalog;
		return qualify( usedCatalog, usedSchema, quotedName );
	}

	public String getQualifiedName(Dialect dialect, String defaultCatalog, String defaultSchema) {
		String quotedName = getQuotedName( dialect );
		String usedSchema = schema == null ? defaultSchema : getQuotedSchema( dialect );
		String usedCatalog = catalog == null ? defaultCatalog : catalog;
		return qualify( usedCatalog, usedSchema, quotedName );
	}

	/**
	 * @return
	 */
	public Vector<FieldMetaData> getFields() {
		if (fields == null) {
			fields = new Vector<FieldMetaData>();
		}
		return fields;
	}

	/**
	 * @param fmd
	 * @return
	 * @throws Exception
	 */
	public TableMetaData addField(FieldMetaData fmd) throws Exception {
		fmd.setDialect(getDialect());
		fmd.setDBMetaData(dbmd());
		getFields().add(fmd);
		if (this.columnIndex == null) columnIndex = new HashMap<String, Integer>();
		int idx = getFields().indexOf(fmd); 
		//System.out.println("TableMetaData.addField().idx = "+idx);
		columnIndex.put(fmd.getName(), new Integer(idx));
		//System.out.println("xxx: " + fmd.getName() + ", " + fmd);
		columns.put(fmd.getName(), fmd);
		if (fmd.isPK()) {
			if (this.primarykey == null) {
				primarykey = new PrimaryKeyMetaData();
				primarykey.setColumn(fmd.getName());
				primarykey.setDialect(dialect);
				primarykey.setTable(this.getName());
				primarykey.setPkName(getDialect().getPKConstraintPrefix()+this.getName());
			}
		} 
		return this;
	}
	
	/**
	 * @param table
	 * @param name
	 * @param sqlType
	 * @param size
	 * @param decimal
	 * @param isNull
	 * @param value
	 * @return
	 * @throws Exception
	 */
	public TableMetaData addField(String table, String name, int sqlType, int size, int decimal, boolean isNull, String value) throws Exception {
		return this.addField(table, name, sqlType, size, decimal, isNull, value, false, false, false); 
	}
	
	/**
	 * 
	 * @param table
	 * @param name
	 * @param sqlType
	 * @param size
	 * @param decimal
	 * @param nullable
	 * @param value
	 * @param pk
	 * @param unique
	 * @param autoincrement
	 * @return
	 * @throws Exception
	 */
	public TableMetaData addField(
		String table, 
		String name, 
		int sqlType, 
		int size, 
		int decimal, 
		boolean nullable, 
		String value,
		boolean pk, 
		boolean unique, 
		boolean autoincrement
	) throws Exception {
		if (pk && nullable) {
			throw new Exception("Cannot define PRIMARY KEY constraint on nullable column");
		}
		FieldMetaData fmd = new FieldMetaData();
		fmd.setDialect(getDialect());
		fmd.setDBMetaData(dbmd());
		
		fmd.setTable(table);
		fmd.setName(name);
		fmd.setSqlType(sqlType);
		fmd.setSize(size);
		fmd.setDecimal(decimal);
		fmd.setNullable(nullable);
		if (!nullable) {
			fmd.setValue(value);
		}
		//System.out.println("pk = "+pk);
		fmd.setPK(pk);
		fmd.setUnique(unique);
		fmd.setAutoincrement(autoincrement);
		
		addField(fmd);
		return this;		
	}
	/*
	public TableMetaData addField(MColumn column) throws Exception {
		FieldMetaData fmd = new FieldMetaData();
		fmd.setDialect(getDialect());
		fmd.setDBMetaData(dbmd());
		fmd.set(column);
		addField(fmd);		
		return this;
	}
	*/
	/**
	 * @param key
	 * @return
	 * @throws Exception
	 */
	public FieldMetaData getFieldByName(String key) throws Exception {
		//if (columnIndex == null)  return null;
		Object obj = columnIndex.get(key);
		if (obj == null) return null;
		int idx = ((Integer)obj).intValue();
		return (FieldMetaData)getFields().get(idx); 
	}
	
	/**
	 * @param pkmd
	 */
	public void setPrimaryKey(PrimaryKeyMetaData pkmd) {
		try {
			String column = pkmd.getColumn();
			FieldMetaData fmd = this.getFieldByName(column);
			if (fmd == null) {
				new Exception("Cannot define PRIMARY KEY constraint on nullable column");
			} else {
				if (fmd.isNullable()) {
					new Exception("Cannot define PRIMARY KEY constraint on nullable column");
				} else {
					this.primarykey = pkmd;					
				}
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	/**
	 * @return
	 */
	public PrimaryKeyMetaData getPrimaryKey() {
		return primarykey;
	}

	/**
	 * @param ixmd
	 */
	public void setIndexes(IndexesMetaData ixmd) {
		this.indexes = ixmd;
	}

	/**
	 * @return
	 */
	public IndexesMetaData getIndexes() {
		return indexes;
	}

	public static String qualify(String catalog, String schema, String table) {
		StringBuffer qualifiedName = new StringBuffer();
		if ( catalog != null ) {
			qualifiedName.append( catalog ).append( '.' );
		}
		if ( schema != null ) {
			qualifiedName.append( schema ).append( '.' );
		}
		return qualifiedName.append( table ).toString();
	}

	// -----  -----------------------------------------------------------------
	
	/**
	 * @return
	 */
	public Dialect getDialect() {
		return dialect;
	}

	/**
	 * @param dialect
	 */
	public void setDialect(Dialect dialect) {
		this.dialect = dialect;
	}

	/**
	 * @return the dbmd
	 */
	public DBMetaData dbmd() {
		return this.dbmd;
	}

	/**
	 * @return the dbmd
	 */
	public DBMetaData getDBMetaData() {
		return this.dbmd;
	}

	/**
	 * @param dbmd the dbmd to set
	 */
	public void setDBMetaData(DBMetaData dbmd) {
		this.dbmd = dbmd;
	}

	/**
	 * @return the schema
	 */
	public String getSchema() {
		return schema;
	}

	/**
	 * @param schema the schema to set
	 */
	public void setSchema(String schema) {
		this.schema = schema;
	}

	/**
	 * @return the catalog
	 */
	public String getCatalog() {
		return catalog;
	}

	/**
	 * @param catalog the catalog to set
	 */
	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}

	// ----------------------- -------------------------------------------------

	public String format() throws Exception {
		String s = "----------------------------------------\n";
		s += "\n";
		s += "Table: [ " + getName() + " ]\n";
		s += "Fields: \n";

		for (int i = 0; i < getFields().size(); i++) {
			FieldMetaData fmd = (FieldMetaData) getFields().elementAt(i);
			s += "  " + fmd.toString() + "\n";
		}
		s += "\n";
		s += "Primayry Key: \n";
		if (getPrimaryKey() != null) {
			s += "  " + getPrimaryKey().toString() + " ]\n";
		}
		s += "\n";
		
		s += "Indexes: \n";
		return s;
	}

	// ----------------------- -------------------------------------------------

	public String readAsDDL() throws Exception {
		CreateTable sql = new CreateTable(dbmd).setTableMetaData(this);
		return sql.toSQLString();
	}

	public HashMap<String, Integer> getColNameIndex() {
		return columnIndex;
	}

	// -----  -----------------------------------------------------------------
	
	/**
	 * Funkce p�etransformuje objekt typu TableMetaData do XML
	 * @param 
	 * @return String - n�vratov� hodnota je �et�zec XML
	 */
	public String transToXML() throws Exception {
		DOMElement node = new DOMElement("TableMetaData");
		transToXML(node);
		return node.asXML();
	}

	/**
	 * Funkce p�etransformuje objekt typu TableMetaData do objekt typu DOMElement 
	 * @param DOMElement node 
	 * @return DOMElement
	 */
	public DOMElement transToXML(DOMElement node) throws Exception {
		node.setName("TMD");
		node.addAttribute("name", ""+this.name);
		if (fields != null && !fields.isEmpty()) {
			DOMElement child = new DOMElement("fields");
			for (Iterator<FieldMetaData> i = getFields().iterator(); i.hasNext();) {
				FieldMetaData field = (FieldMetaData) i.next();
				child.appendChild(field.transToXML(new DOMElement("FMD")));
			}
			node.appendChild(child);	
		}
		if (primarykey != null) {
			node.appendChild(primarykey.transToXML(new DOMElement("PKMD")));
		}
		if (indexes != null && !indexes.isEmpty()) {
			DOMElement child = new DOMElement("indexes");
			child.appendChild(indexes.transToXML(new DOMElement("IXMD")));
			node.appendChild(child);			
		}
		return null;
	}
	
	/**
	 * Funkce p�etransformuje �et�zec XML na objekt typu UniQLExpr 
	 * @param String xml 
	 * @return TableMetaData
	 */
	public TableMetaData xmlToExpr(String xml) throws Exception {
		Document doc = DocumentHelper.parseText(xml);
		Element root = doc.getRootElement();
		return this.xmlToExpr(root);
	}

	/**
	 * Funkce p�etransformuje objekt typu org.dom4j.Element na objekt typu UniQLExpr 
	 * @param Element node
	 * @return TableMetaData
	 */	
	public TableMetaData xmlToExpr(Element node) throws Exception {
		String name = node.attributeValue("name");
		this.setName(name);
		ArrayList<Element> sections = new ArrayList<Element>(node.elements());
		for (int i = 0; i < sections.size(); i++) {
			Element section = sections.get(i);
			String nodename = section.getName();
			if (nodename.equals("fields")) {
				ArrayList<Element> list = new ArrayList<Element>(section.elements());
				for (int j = 0; j < list.size(); j++) {
					FieldMetaData fmd = new FieldMetaData();
					fmd.setDialect(getDialect());
					fmd.xmlToExpr(list.get(j)); 
					this.addField(fmd);
				}
			}
			if (nodename.equals("PKMD")) {
				PrimaryKeyMetaData pkmd = new PrimaryKeyMetaData();
				pkmd.setDialect(this.getDialect());
				pkmd.xmlToExpr(section);
				System.out.println("pkmd = "+pkmd);
				this.setPrimaryKey(pkmd);
				System.out.println("pkmd(0) = "+this.getPrimaryKey());
			}
			if (nodename.equals("indexes")) {
				IndexesMetaData ixmd = new IndexesMetaData();
				ixmd.setDialect(this.getDialect());
				ixmd.setTable(name);
				ixmd.xmlToExpr(section.element("IXMD"));
				this.setIndexes(ixmd);
			}
		}
		System.out.println("UniQLExpr.xmlToExpr( END ------------ )");
		return this;
	}
}
